using System;
using System.Collections.Generic;
using System.Text;
using System.Diagnostics;
using EditWebSite.Database;
using System.Web.Services.Protocols;
using System.Text.RegularExpressions;
using EditWebSite.LocalObjects;

namespace EditWebSite.Logics
{
    internal class DatabaseHelper
    {
        private bool _LavoraOnLine;

        public DatabaseHelper(bool LavoraOnLine)
        {
            _LavoraOnLine = LavoraOnLine;
        }

        private string getConnectionStringOffline()
        {
            string Password = "";
            string Database = System.IO.Path.Combine(System.IO.Path.Combine(System.Windows.Forms.Application.StartupPath, "Database"), "doc.mdb");
            return "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Jet OLEDB:Database Password=" + Password + ";Data Source=" + Database;
        }

        private Dictionary<string, EditWebSite.Database.Tabella> _Tabelle;

        internal Dictionary<string, EditWebSite.Database.Tabella> GetTabelle()
        {
            try
            {
                if (_Tabelle == null)
                {
                    if (_LavoraOnLine)
                    {
                        Database.Database db = new Database.Database();
                        EditWebSite.Database.Tabella[] tabs = db.GetTabelle();
                        _Tabelle = new Dictionary<string, EditWebSite.Database.Tabella>();
                        for (int i = 0; i < tabs.Length; i++)
                        {
                            _Tabelle.Add(tabs[i].Nome, tabs[i]);
                        }
                    }
                    else
                    {
                        Connection con = new Connection(getConnectionStringOffline());
                        Proprietario[] prop = con.GetProprietari();

                        #region Conversione oggetti per lavoro offline
                        _Tabelle = new Dictionary<string, EditWebSite.Database.Tabella>();
                        for (int i = 0; i < prop[0].Tabelle.Count; i++)
                        {
                            EditWebSite.Database.Tabella tab = new EditWebSite.Database.Tabella();
                            tab.Alias = prop[0].Tabelle[i].Alias;
                            List<EditWebSite.Database.Colonna> eleCol = new List<EditWebSite.Database.Colonna>();
                            for (int j = 0; j < prop[0].Tabelle[i].Colonne.Length; j++)
                            {
                                EditWebSite.Database.Colonna col = new EditWebSite.Database.Colonna();
                                col.Descrizione = prop[0].Tabelle[i].Colonne[j].Descrizione;
                                col.Nome = prop[0].Tabelle[i].Colonne[j].Nome;
                                switch (prop[0].Tabelle[i].Colonne[j].Tipo)
                                {
                                    case EditWebSite.LocalObjects.Colonna.TipoColonna.Numerico:
                                        col.Tipo = TipoColonna.Numerico;
                                        break;
                                    case EditWebSite.LocalObjects.Colonna.TipoColonna.Stringa:
                                        col.Tipo = TipoColonna.Stringa;
                                        break;
                                    case EditWebSite.LocalObjects.Colonna.TipoColonna.Blob:
                                        col.Tipo = TipoColonna.Blob;
                                        break;
                                    case EditWebSite.LocalObjects.Colonna.TipoColonna.Altro:
                                        col.Tipo = TipoColonna.Altro;
                                        break;
                                    default:
                                        break;
                                }

                                eleCol.Add(col);
                            }
                            tab.Colonne = eleCol.ToArray();
                            tab.Descrizione = prop[0].Tabelle[i].Descrizione;
                            tab.Identificativo = prop[0].Tabelle[i].Identificativo;
                            List<EditWebSite.Database.Indice> eleInd = new List<EditWebSite.Database.Indice>();
                            for (int j = 0; j < prop[0].Tabelle[i].Indici.Length; j++)
                            {
                                EditWebSite.Database.Indice ind = new EditWebSite.Database.Indice();
                                ind.Campi = prop[0].Tabelle[i].Indici[j].Campi;
                                ind.Descrizione = prop[0].Tabelle[i].Indici[j].Descrizione;
                                ind.Nome = prop[0].Tabelle[i].Indici[j].Nome;

                                eleInd.Add(ind);
                            }
                            tab.Indici = eleInd.ToArray();
                            tab.Nome = prop[0].Tabelle[i].Nome;

                            _Tabelle.Add(tab.Nome, tab);
                        }
                        #endregion

                    }
                }
                return _Tabelle;
            }
            catch (SoapException soapEx)
            {
                #region LOGIC
                throw new Exception(GestException.ParseExceptionDatabase(soapEx));
                #endregion
            }
            catch (Exception)
            {
                #region LOGIC
                throw;
                #endregion
            }
        }



        internal int ExecuteQuery(string Sql)
        {
            try
            {
                if (_LavoraOnLine)
                {
                    Database.Database db = new Database.Database();
                    db.UserValue = Program.Utente;
                    return db.ExecuteQuery(Sql);
                }
                else
                {
                    Connection db = new Connection(getConnectionStringOffline());
                    return db.EseguiQuery(Sql);                
                }
            }
            catch (SoapException soapEx)
            {
                #region LOGIC
                throw new Exception(GestException.ParseExceptionDatabase(soapEx));
                #endregion
            }
            catch (Exception)
            {
                #region LOGIC
#if DEBUG
                Debugger.Break();
#endif
                throw;
                #endregion
            }
        }

        internal int ExecuteQueryWithConfirm(string Sql)
        {
            try
            {
                if (_LavoraOnLine)
                {
                    Database.Database db = new Database.Database();
                    db.UserValue = Program.Utente;

                    db.TransactionStart();
                    int righe = db.ExecuteQuery(Sql);
                    if (EditWebSite.Presentation.Messaggi.Confirm("Si stanno per modificare " + righe + ".\r\nContinuare?"))
                    {
                        db.TransactionCommit();
                        return righe;
                    }
                    else
                    {
                        db.TransactionRollback();
                        return -1;
                    }
                }
                else
                {
                    Connection db = new Connection(getConnectionStringOffline());
                    

                    db.TransactionStart();
                    int righe = db.EseguiQuery(Sql);
                    if (EditWebSite.Presentation.Messaggi.Confirm("Si stanno per modificare " + righe + ".\r\nContinuare?"))
                    {
                        db.TransactionCommit();
                        return righe;
                    }
                    else
                    {
                        db.TransactionRollback();
                        return -1;
                    }
                }

            }
            catch (SoapException soapEx)
            {
                #region LOGIC
                throw new Exception(GestException.ParseExceptionDatabase(soapEx));
                #endregion
            }
            catch (Exception)
            {
                #region LOGIC
#if DEBUG
                Debugger.Break();
#endif
                throw;
                #endregion
            }
        }


        internal System.Data.DataTable GetDataTable(string Sql)
        {
            try
            {
                if (_LavoraOnLine)
                {
                    Database.Database db = new Database.Database();
                    db.UserValue = Program.Utente;
                    return db.GetDataTable(Sql);
                }
                else
                {
                    Connection db = new Connection(getConnectionStringOffline());
                    return db.GetDataTable(Sql);
                }
            }
            catch (SoapException soapEx)
            {
                #region LOGIC
                throw new Exception(GestException.ParseExceptionDatabase(soapEx));
                #endregion
            }
            catch (Exception)
            {
                #region LOGIC
#if DEBUG
                Debugger.Break();
#endif
                throw;
                #endregion
            }
        }

        internal bool SqlIsSelect(string Sql)
        {
            try
            {
                string[] parole = Sql.Split(' ');
                Regex exp = new Regex("DELETE|INSERT|PROCEDURE|UPDATE");
                for (int i = 0; i < parole.Length; i++)
                {
                    if (exp.Match(parole[i].ToUpper().Trim()).Success)
                    {
                        return false;
                    }
                }
                return true;
            }
            catch (Exception)
            {
                #region LOGIC
#if DEBUG
                Debugger.Break();
#endif
                throw;
                #endregion
            }
        }





        internal string GetTestoFormattato(string testo)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("StringBuilder sSql = new StringBuilder();");

                string[] linee = testo.Split('\n');

                for (int i = 0; i < linee.Length; i++)
                {
                    sb.AppendLine("sSql.AppendLine(\"" + linee[i].TrimEnd() + " \");");
                }


                return sb.ToString();
            }
            catch (Exception)
            {
                #region LOGIC
#if DEBUG
                Debugger.Break();
#endif
                throw;
                #endregion
            }
        }
    }
}
